﻿CREATE PROCEDURE [maint].[GetDeviceRestoreSet]
@DbName [sysname], @DeviceName [sysname], @CurrentPosition INT, @BackupType INT, @RestorePoint DATETIME, @NextPosition INT OUTPUT, @BackupStartDate DATETIME=NULL OUTPUT, @BackupFinishDate DATETIME=NULL OUTPUT, @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int, @ERROR int
	SET @RC = 0

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID)
			PRINT N'	@DbName = N''' + ISNULL(@DbName,N'NULL') + ''''
			PRINT N'	@DeviceName = N''' + ISNULL(@DeviceName,N'NULL') + ''''
			PRINT N'	@CurrentPosition = N''' + ISNULL(CAST(@CurrentPosition AS nvarchar),N'NULL') + ''''
			PRINT N'	@BackupType = N''' + ISNULL(CAST(@BackupType AS nvarchar),N'NULL') + ''''
			PRINT N'	@RestorePoint = N''' + ISNULL(CAST(@RestorePoint AS nvarchar(20)),N'NULL') + ''''
		END

	EXEC @RC = util.CheckDevice @DeviceName
	IF(@RC <> 0)
		GOTO EXIT_PROC

	DECLARE @OutputTableName nvarchar(36)
	EXEC @RC = [maint].[GetDeviceHeader] @DbName, @DeviceName, @OutputTableName OUTPUT, @OutputDiagnostics;
	SET @ERROR = @@ERROR
	IF(@RC <> 0)
	OR(@ERROR <> 0)
		GOTO ERROR_HANDLER

	--get backup name and expiration date
	DECLARE @PositionQuery nvarchar(512)

	IF(@OutputDiagnostics = 1)
		BEGIN
			SET @PositionQuery = N'SELECT [BackupDescription], [Position], [BackupType], [BackupStartDate], [BackupFinishDate] FROM ' + @OutputTableName
			EXEC(@PositionQuery)
		END;

	DECLARE @FullPositionQuery nvarchar(512)
	SET @FullPositionQuery = N'
SELECT TOP 1
	@NextPosition = [Position]
FROM ' + @OutputTableName + ' 
WHERE  ([BackupType] = @BackupType)
	AND([BackupStartDate] < ISNULL(@RestorePoint,GETDATE()))
ORDER BY [Position] DESC';

	DECLARE @DiffPositionQuery nvarchar(512)
	SET @DiffPositionQuery = N'
SELECT TOP 1
	@NextPosition = [Position]
FROM ' + @OutputTableName + ' 
WHERE  ([BackupType] = @BackupType)
	AND([Position] = (
			SELECT MAX([Position])
			FROM ' + @OutputTableName + '
			WHERE  ([BackupType] = @BackupType)
				AND([Position] > @CurrentPosition)
				AND([BackupFinishDate] < ISNULL(@RestorePoint,GETDATE()))
			)
		)
ORDER BY [Position] DESC';

	DECLARE @LogPositionQuery nvarchar(512)
	SET @LogPositionQuery = N'
SELECT TOP 1
	@NextPosition = [Position]
	,@BackupStartDate = [BackupStartDate]
	,@BackupFinishDate = [BackupFinishDate]
FROM ' + @OutputTableName + ' 
WHERE  ([BackupType] = @BackupType)
	AND([Position] = (
			SELECT MIN([Position])
			FROM ' + @OutputTableName + '
			WHERE  ([BackupType] = @BackupType)
				AND([Position] > @CurrentPosition)
				AND([BackupStartDate] <= ISNULL(@RestorePoint,GETDATE()))
			)
		)
ORDER BY [Position]';

	SET @PositionQuery = 
		(CASE @BackupType
			WHEN 1 THEN @FullPositionQuery
			WHEN 5 THEN @DiffPositionQuery
			ELSE @LogPositionQuery
		END);

	IF(@OutputDiagnostics = 1)
		PRINT N'	@PositionQuery = ' + CHAR(10) + ISNULL(@PositionQuery,N'NULL') + CHAR(10)

	SET @NextPosition = NULL;

	EXEC @RC = sp_executesql @PositionQuery
		,N'@CurrentPosition int, @BackupType int, @RestorePoint datetime, @NextPosition int OUTPUT, @BackupStartDate datetime OUTPUT, @BackupFinishDate datetime OUTPUT'
		,@CurrentPosition, @BackupType, @RestorePoint, @NextPosition OUTPUT, @BackupStartDate OUTPUT, @BackupFinishDate OUTPUT;
	IF(@RC <> 0)
		BEGIN
			RAISERROR('Device %s does not contain a valid DBA database backup',16,1,@DeviceName)
			GOTO ERROR_HANDLER
		END

	GOTO EXIT_PROC

ERROR_HANDLER:
	IF(@RC = 0)
		SET @RC = @ERROR

EXIT_PROC:
	IF(@RC = 0)

	RETURN(@RC)	
END;